package com.cs2340.taskplusplus;

import java.util.ArrayList;
import java.util.Date;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class TaskListDatabase
{
	public static final String columns = "Parent_Table char(300), Name char(50), Description char(300), Labels char(100), Date char(100), Latitude integer, Longitude integer, isDone bit, isList bit";

	public static void newTaskList(TaskList t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		db.execSQL("CREATE TABLE [" + t.getTableName() + "] " + "(" + TaskListDatabase.columns + ");");
		db.execSQL("CREATE TABLE [" + t.getLabelTable() + "] (Parent_Table char(100), Name char(100));");
		for (Label l : t.getLabels())
		{
			db.execSQL("INSERT INTO [" + t.getLabelTable() + "] VALUES (\"" + t.getTableName() + "\", \""  + l.getName() + "\");");			
		}
		db.close();
	}
	
	public static void addToParent(TaskList t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		db.execSQL("INSERT INTO [" + t.getParentTable() + "] VALUES(\"" + t.getParentTable() + "\", \"" + t.getName() + "\", \"" + t.getDescription() + "\", \"" + t.getLabelTable() + "\", \"" + t.getDate().toString() + "\", " + t.getLocation().getLat() + ", " + t.getLocation().getLon() + ", 0, 1)");
		db.close();
	}
	
	public static void fillInfo(TaskList t) 
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		Cursor selfInfo = db.query("["+t.parentTable+"]", new String[] {"Description","isDone","Labels","Latitude", "Longitude", "Date"}, "Name=\""+t.name+"\"", null,null,null,null);
		selfInfo.moveToFirst();
		t.description = selfInfo.getString(selfInfo.getColumnIndex("Description"));
		t.date = new Date(selfInfo.getString(selfInfo.getColumnIndex("Date")));
		t.labelTable = selfInfo.getString(selfInfo.getColumnIndex("Labels"));
		t.location = new Location(selfInfo.getInt(selfInfo.getColumnIndex("Latitude")), selfInfo.getInt(selfInfo.getColumnIndex("Longitude")));
		boolean isDone = selfInfo.getInt(selfInfo.getColumnIndex("isDone"))>0;
		selfInfo.close();
		db.close();
		if (isDone)
			t.setDone();
		else
			t.setNotDone();
		
		db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		Cursor labelInfo = db.query("["+t.getLabelTable()+"]", new String[] {"Parent_Table","Name"}, null, null,null,null,null);
		labelInfo.moveToFirst();
		
		ArrayList<Label> labels = new ArrayList<Label>();
		String labelName, labelParent;
		for (int j = 0; j < labelInfo.getCount(); j++)
		{
			labelParent = labelInfo.getString(labelInfo.getColumnIndex("Parent_Table"));
			labelName = labelInfo.getString(labelInfo.getColumnIndex("Name"));
			labels.add(new Label(labelParent, labelName));
			labelInfo.moveToNext();
		}
		labelInfo.close();
		db.close();
		t.setLabels(labels);
	}
	
	public static void newList(TaskList t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		db.execSQL("DELETE FROM \"" + t.getTableName() + "\"");
		db.close();
	}
	
	public static void newLabels(TaskList t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		db.execSQL("DELETE FROM \"" + t.getLabelTable() + "\"");
		db.close();
	}

	public static void buildList(TaskList t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		Cursor infoGetter = db.query("["+t.getTableName()+"]", new String[] {"Parent_Table","Name", "isList"}, null, null, null, null, null);
		int isList;
		String name2, parent2;
		ArrayList<Doable> list = new ArrayList<Doable>();
		infoGetter.moveToFirst();
		for (int i = 0; i < infoGetter.getCount(); i++)
		{
			isList = infoGetter.getInt(infoGetter.getColumnIndex("isList"));
			parent2 = infoGetter.getString(infoGetter.getColumnIndex("Parent_Table"));
			name2 = infoGetter.getString(infoGetter.getColumnIndex("Name"));
			if (isList > 0)
			{
				db.close();
				list.add(new TaskList(parent2+"_"+name2));
				db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
			}
			else
			{
				db.close();
				list.add(new Task(parent2, name2));
				db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
			}
			infoGetter.moveToNext();
		}
		infoGetter.close();
		db.close();
		t.setList(list);
	}
	
	public static void expand(TaskList t)
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);
		db.execSQL("CREATE TABLE [" + t.getTableName() + "] " + "(" + TaskListDatabase.columns + ");");
		Cursor infoGetter = db.query("["+t.getParentTable()+"]", new String[] {"Description","isDone","Labels", "Date"}, "Name=\"" + t.getName() + "\"", null, null, null, null);
		infoGetter.moveToFirst();
		t.description = infoGetter.getString(infoGetter.getColumnIndex("Description"));
		t.date = new Date(infoGetter.getString(infoGetter.getColumnIndex("Date")));
		t.labelTable = infoGetter.getString(infoGetter.getColumnIndex("Labels"));
		boolean isDone = infoGetter.getInt(infoGetter.getColumnIndex("isDone"))>0;
		infoGetter.close();
		db.close();
		if (isDone)
			t.setDone();
		else
			t.setNotDone();
		TaskListDatabase.buildLabels(t);
	}
	
	public static void buildLabels(TaskList t) 
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);
		Cursor labelInfo = db.query("["+t.getLabelTable()+"]", new String[] {"Parent_Table","Name"}, null, null,null,null,null);
		labelInfo.moveToFirst();
		String labelName, labelParent;
		ArrayList<Label> labels = new ArrayList<Label>();
		for (int j = 0; j < labelInfo.getCount(); j++)
		{
			labelParent = labelInfo.getString(labelInfo.getColumnIndex("Parent_Table"));
			labelName = labelInfo.getString(labelInfo.getColumnIndex("Name"));
			labels.add(new Label(labelParent, labelName));
		}
		labelInfo.close();
		db.close();
		t.setLabels(labels);
	}

	public static void addTask(TaskList t, Doable task)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);		
		int i = 0;
		if (task.isList())
			i = 1;
		int j = 0;
		if (task.isDone())
			j = 1;
		db.execSQL("INSERT INTO [" + t.getTableName() + "] VALUES (\""+task.getParentTable()+"\", \"" + task.getName() + "\", \""+task.getDescription()+"\", \"" + task.getLabelTable() + "\", \"" + task.getDate().toString() + "\", " + task.getLocation().getLat() + ", " + task.getLocation().getLon() + ", "+j+", "+i+");");
		db.close();
	}

	public static void removeTask(TaskList t, Doable task)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);		
		db.execSQL("DELETE FROM [" + t.getTableName() + "] WHERE Name=\"" + task.getName() + "\"");
		db.close();
	}
	
	public static void delete(TaskList t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);		
		if (t.getLabelTable() != null)
			db.execSQL("DROP TABLE \"" + t.getLabelTable() + "\"");
		db.execSQL("DROP TABLE \"" + t.getTableName() + "\"");
		db.close();
	}
	
	public static void addLabel(TaskList t, Label l)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);		
		db.execSQL("INSERT INTO [" + t.getLabelTable() + "] VALUES (\""+t.getTableName()+"\", \"" + l.getName() + "\");");
		db.close();
	}
	
	public static void removeLabel(TaskList t, Label l)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);		
		db.execSQL("DELETE FROM " + t.getLabelTable() + " WHERE Name=\""+l.getName()+"\"");
		db.close();		
	}
	
	public static void setDone(TaskList t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		db.execSQL("UPDATE ["+t.getTableName() + "] SET isDone=1");
		if (t.getParentTable() != null)
			db.execSQL("UPDATE ["+t.getParentTable() + "] SET isDone=1 WHERE Name=\""+t.getName()+"\"");
		db.close();
	}
	public static void setNotDone(TaskList t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		db.execSQL("UPDATE ["+t.getTableName() + "] SET isDone=0");
		if (t.getParentTable() != null)
			db.execSQL("UPDATE ["+t.getParentTable() + "] SET isDone=0 WHERE Name=\""+t.getName()+"\"");
		db.close();
	}
}
